package com.budwk.app.sqfw.controllers.admin.dataMigration;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.date.LocalDateTimeUtil;
import com.alibaba.fastjson.JSONObject;
import com.budwk.app.sqfw.enums.SupportBusinessTypeEnum;
import com.budwk.app.sqfw.models.PersonInfo;
import com.budwk.app.sqfw.models.Sqfw_formSummary;
import com.budwk.app.sqfw.models.Sqfw_formSummary_new;
import com.budwk.app.sqfw.services.PersonInfoService;
import com.budwk.app.sqfw.services.SqfwCenSusPaperService;
import com.budwk.app.sqfw.services.SqfwFormSummaryService;
import com.budwk.app.sqfw.services.SqfwFormsummaryNewService;
import com.budwk.app.sqfwV2.constants.ProblemConstant;
import com.budwk.app.sqfwV2.dto.dto.PersonInfoDTO;
import com.budwk.app.sqfwV2.dto.dto.WasteCategoryNewPdfDTO;
import com.budwk.app.sqfwV2.dto.dto.childrenVisits.ChildrenVisitationPdfDTO;
import com.budwk.app.sqfwV2.dto.entity.SqfwProblemInfo;
import com.budwk.app.sqfwV2.service.DayingRoadService;
import com.budwk.app.sqfwV2.service.ProblemInfoService;
import com.budwk.app.sqfwV2.service.ShopsBaseInfoService;
import com.budwk.app.sys.models.Sys_dict;
import com.budwk.app.sys.services.SysDictService;
import com.budwk.app.util.StrUtil;
import com.budwk.app.util.poiUtils.nutzBeanUtils.NutzIocUtils;
import com.budwk.starter.common.result.Result;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.nutz.aop.interceptor.ioc.TransAop;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.nutz.dao.util.cri.SqlExpressionGroup;
import org.nutz.ioc.aop.Aop;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.LinkedList;
import java.util.List;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.stream.Collectors;

@IocBean
@Slf4j
public class SqfwDataSystemComponent {

    @Inject
    private SqfwCenSusPaperService sqfwCenSusPaperService;
    @Inject
    private SqfwFormSummaryService sqfwFormSummaryService;
    @Inject
    private SqfwFormsummaryNewService sqfwFormsummaryNewService;
    @Inject
    private ShopsBaseInfoService shopsBaseInfoService;
    @Inject
    private DayingRoadService dayingRoadService;
    @Inject
    private ProblemInfoService problemInfoService;
    @Inject
    private SysDictService sysDictService;
    @Inject("taskQueryExecutor")
    private ThreadPoolExecutor taskQueryExecutor;


    public List<Object> queryTableData(String name, String id) {
        Dao dao = (Dao) NutzIocUtils.getBeanByType(Dao.class);
        Sql sql = Sqls.create("select * FROM shequ.$table $condition");
        sql.vars().set("table", name);
        if (StringUtils.isNotEmpty(id)) {
            sql.setCondition(Cnd.where("id","=", id));
        }
        sql.setCallback(new SqlCallback() {
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                List<JSONObject> list = new LinkedList<>();
                while (rs.next()) {
                    JSONObject jsonObject = new JSONObject();
                    int colIndex = 1;
                    while (true) {
                        try {
                            String columnName = rs.getMetaData().getColumnName(colIndex);
                            jsonObject.put(columnName, rs.getObject(columnName));
                            colIndex ++;
                        } catch (Exception e) {
                            break;
                        }
                    }
                    list.add(jsonObject);
                }
                return list;
            }
        });
        dao.execute(sql);
        List<Object> list = sql.getList(Object.class);
        return list;
    }

    private void insertChild(Sys_dict dict, String[] names, String[] codes, List<Sys_dict> list) {
        for (int i = 0; i < codes.length; i++) {
            String code = codes[i].trim();
            List<Sys_dict> datas = sysDictService.query(Cnd.where(Sys_dict::getCode, "=", code));
            if (!datas.isEmpty()) {
                continue;
            }
            Sys_dict dict1 = new Sys_dict();
            dict1.setName(names[i].trim());
            dict1.setCode(code);
            dict1.setUnitid(null);
            dict1.setLocation(i+1);
            sysDictService.save(dict1, dict.getId());
            list.add(dict1);
        }
    }

    private Sys_dict insertParentData(String code, String name) {
        List<Sys_dict> list = sysDictService.query(Cnd.where(Sys_dict::getCode, "=", code));
        Sys_dict dict = new Sys_dict();
        if (list.isEmpty()) {
            dict.setName(name);
            dict.setCode(code);
            dict.setUnitid(null);
            dict.setLocation(1);
            sysDictService.save(dict, null);
        } else {
            dict = list.get(0);
        }
        return dict;
    }

    public Result<?> initProblem() {
        List<SqfwProblemInfo> list = new ArrayList<>();
        buildProblem(ProblemConstant.BAIYUN_SAFETY_CHECK_ELEC_SEVEN, 1,"isParkeCharged", "true", "是否在规定区域停放、充电", "电动车未在规定区域停放、充电", "要求在规范区域停放、充电", list);
        return Result.success();
    }

    private void buildProblem(String problemCode, Integer riskCode, String fields, String defaultValue, String riskDesc, String harardDesc, String remark, List<SqfwProblemInfo> list) {
        try {
            SqfwProblemInfo s = new SqfwProblemInfo();
            s.setRiskCode(riskCode)
                    .setFields(fields)
                    .setDefaultValue(defaultValue)
                    .setRiskDesc(riskDesc)
                    .setHarardDesc(harardDesc)
                    .setRemark(remark)
                    .setProblemCode(problemCode)
                    .setProblemTitle("是否有电动自行车问题枚举")
                    .setType("白云安全排查");
            problemInfoService.insertProblem(s);
            list.add(s);
        } catch (Exception e) {
            log.error("erropr", e);
        }
    }

    public Result<?> ljRemove() {
        Cnd cnd = Cnd.NEW();
        SqlExpressionGroup where = cnd.where();
        where.andEquals(Sqfw_formSummary::getType, "垃圾分类");
//        where.andEquals(Sqfw_formSummary::getDistrict, "观山湖区");
//        where.andEquals(Sqfw_formSummary::getCounty, "油榨街道");
        cnd.getOrderBy().asc(Sqfw_formSummary_new::getFillDate);
        List<Sqfw_formSummary> list = sqfwFormSummaryService.query(cnd);
        if (list.size() == 0) {
            return Result.success();
        }

        List<Sqfw_formSummary_new> formSummaryData = new ArrayList<>();
        for (Sqfw_formSummary sqfwFormSummary : list) {
            String isComplateMigrate = sqfwFormSummary.getIsComplateMigrate();
            if (StringUtils.isNotEmpty(isComplateMigrate) && "2".equals(isComplateMigrate)) {
                continue;
            }
            String detailAddress = String.format("%s%s%s%s%s%s", StrUtil.formatStr(sqfwFormSummary.getProvince()), StrUtil.formatStr(sqfwFormSummary.getCity()),
                    StrUtil.formatStr(sqfwFormSummary.getDistrict()),
                    StrUtil.formatStr(sqfwFormSummary.getCounty()),
                    StrUtil.formatStr(sqfwFormSummary.getCommunity()),
                    StrUtil.formatStr(sqfwFormSummary.getA3()));
            Sqfw_formSummary_new formSummaryNew = new Sqfw_formSummary_new();
            formSummaryNew.setSqfwId(sqfwFormSummary.getSqfwId())
                    .setId(sqfwFormSummary.getId())
                    .setSqfwFlowId(sqfwFormSummary.getSqfwFlowId())
                    .setUnitId(sqfwFormSummary.getUnitId())
                    .setCreateDate(sqfwFormSummary.getCreateDate())
                    .setCreateBy(sqfwFormSummary.getCreateBy())
                    .setUpdateDate(sqfwFormSummary.getUpdateDate())
                    .setUpdateBy(sqfwFormSummary.getUpdateBy())
                    .setCreateUserName(sqfwFormSummary.getCreateUserName())
                    .setCreateUserPhone(sqfwFormSummary.getCreateUserPhone())
                    .setType(sqfwFormSummary.getType())
                    .setUnitName(sqfwFormSummary.getUnitName())
                    .setFillDate(sqfwFormSummary.getFillDate())
                    .setProvince(sqfwFormSummary.getProvince())
                    .setCity(sqfwFormSummary.getCity())
                    .setDistrict(sqfwFormSummary.getDistrict())
                    .setCounty(sqfwFormSummary.getCounty())
                    .setCommunity(sqfwFormSummary.getCommunity())
                    .setDetailAddress(detailAddress)
                    .setCheckStatus(sqfwFormSummary.getCheckStatus())
                    .setWorkOrderId(sqfwFormSummary.getWorkOrderId());

            WasteCategoryNewPdfDTO pdfDTO = new WasteCategoryNewPdfDTO();
            pdfDTO.setAddress(sqfwFormSummary.getA3())
                    .setProblems(sqfwFormSummary.getA20())
                    .setRectificationType(sqfwFormSummary.getA17())
                    .setRectificationMeasure(sqfwFormSummary.getA18())
                    .setSitePhotos(sqfwFormSummary.getA9())
                    .setFillDate(LocalDateTimeUtil.of(sqfwFormSummary.getFillDate()));
            pdfDTO.setIssueStatus("false");
            if (StringUtils.isNotEmpty(pdfDTO.getProblems())) {
                pdfDTO.setIssueStatus(pdfDTO.getProblems().equals("无") ? "false" : "true");
            }
            WasteCategoryNewPdfDTO f = StrUtil.serializerData(WasteCategoryNewPdfDTO.class, pdfDTO);
            formSummaryNew.setParamData(JSONObject.toJSONString(f));
            formSummaryData.add(formSummaryNew);
        }
        List<Sqfw_formSummary_new> resData = formSummaryData.stream().filter(e -> !DateUtil.format(e.getCreateDate(), DatePattern.NORM_DATETIME_PATTERN).equals("0001-01-01 00:00:00")).collect(Collectors.toList());
        for (Sqfw_formSummary_new formSummary : formSummaryData) {
            String date = DateUtil.format(formSummary.getCreateDate(), DatePattern.NORM_DATETIME_PATTERN);
            if (date.equals("0001-01-01 00:00:00")) {
                continue;
            }
            WasteCategoryNewPdfDTO pdfDTO = JSONObject.parseObject(formSummary.getParamData(), WasteCategoryNewPdfDTO.class);
            Sqfw_formSummary_new summaryNew = queryLastData(formSummary, resData);
            if (summaryNew != null) {
                pdfDTO.setLastSummaryId(summaryNew.getId());
                WasteCategoryNewPdfDTO f = StrUtil.serializerData(WasteCategoryNewPdfDTO.class, pdfDTO);
                formSummary.setParamData(JSONObject.toJSONString(f));
            }
        }
        // 最终数据  formSummaryData
//        for (Sqfw_formSummary_new formSummaryDatum : formSummaryData) {
//            saveDate(formSummaryDatum);
//        }
        for (Sqfw_formSummary_new formSummaryDatum : formSummaryData) {
            saveDate(formSummaryDatum);
        }
        return Result.success();
    }


    private void saveDate(Sqfw_formSummary_new formSummaryDatum) {
        String id = formSummaryDatum.getId();
        Sqfw_formSummary summary = sqfwFormSummaryService.fetch(id);
        String isComplateMigrate = summary.getIsComplateMigrate();
        if (StringUtils.isNotEmpty(isComplateMigrate) && "2".equals(isComplateMigrate)) {
            return;
        }
        summary.setIsComplateMigrate("2");
        sqfwFormSummaryService.updateIgnoreNull(summary);
        Sqfw_formSummary_new formSummary_new = sqfwFormsummaryNewService.fetch(id);
        if (formSummary_new != null) {
            return;
        }
        sqfwFormsummaryNewService.insert(formSummaryDatum);
    }


    private Sqfw_formSummary_new queryLastData(Sqfw_formSummary_new formSummary, List<Sqfw_formSummary_new> formSummaryNews) {
        List<Sqfw_formSummary_new> data = formSummaryNews.stream().filter(e -> e.getDetailAddress().equals(formSummary.getDetailAddress()) && e.getUnitId().equals(formSummary.getUnitId()))
                .sorted(Comparator.comparing(Sqfw_formSummary_new::getFillDate).reversed())
                .collect(Collectors.toList());
        if (data.size() == 0) {
            return null;
        }
        // 取出小于当前提交时间的所有数据
        List<Sqfw_formSummary_new> formSummaryData = data.stream().filter(e -> e.getFillDate().getTime() < formSummary.getFillDate().getTime()).collect(Collectors.toList());
        if (formSummaryData.size() > 0) {
            return formSummaryData.get(0);
        }
        return null;
    }


    @Aop(TransAop.READ_COMMITTED)
    public Result<?> childrenMove() {
        Cnd cnd = Cnd.NEW();
        SqlExpressionGroup where = cnd.where();
        cnd.getOrderBy().asc(Sqfw_formSummary_new::getFillDate);
        where.andEquals(Sqfw_formSummary_new::getType, SupportBusinessTypeEnum.CHILDREN_VISITATION.getName());
        List<Sqfw_formSummary_new> list = sqfwFormsummaryNewService.query(cnd);
        if (CollectionUtil.isEmpty(list)) {
            return Result.success();
        }
        for (Sqfw_formSummary_new formSummary : list) {
            ChildrenVisitationPdfDTO pdfDTO = JSONObject.parseObject(formSummary.getParamData(), ChildrenVisitationPdfDTO.class);
            if (StringUtils.isNotEmpty(pdfDTO.getPersonId())) {
                continue;
            }
            // 插入人员表
            String personId = insertChildren(formSummary);
            ChildrenVisitationPdfDTO f = StrUtil.serializerData(ChildrenVisitationPdfDTO.class, pdfDTO);
            f.setPersonId(personId);
            formSummary.setParamData(JSONObject.toJSONString(f));
            sqfwFormsummaryNewService.updateIgnoreNull(formSummary);
        }
        return Result.success();
    }

    public String insertChildren(Sqfw_formSummary_new formSummary) {
        PersonInfoDTO reqVo = new PersonInfoDTO();
        ChildrenVisitationPdfDTO f = JSONObject.parseObject(formSummary.getParamData(), ChildrenVisitationPdfDTO.class);
        // 后期等待修改..........
        reqVo.setName(f.getVisitedName())
                .setProvince(formSummary.getProvince())
                .setCity(formSummary.getCity())
                .setDistrict(formSummary.getDistrict())
                .setCounty(formSummary.getCounty())
                .setCommunity(formSummary.getCommunity())
                .setCreateBy(formSummary.getCreateBy())
                .setCreateDate(formSummary.getCreateDate() != null ? formSummary.getCreateDate().toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime() : null)
                .setUpdateBy(formSummary.getUpdateBy())
                .setCreateDate(formSummary.getUpdateDate() != null ? formSummary.getUpdateDate().toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime() : null)
                .setIdNumber(f.getIdcard())
                .setAddress(f.getAddress())
                .setRegAddress(f.getHouseholdAddress())
        ;
        // 按照五级+身份证号检查用户是否存在
        Cnd cnd = Cnd.NEW();
        SqlExpressionGroup where = cnd.where();
        where.andEquals(PersonInfo::getProvince, reqVo.getProvince())
                .andEquals(PersonInfo::getCity, reqVo.getCity())
                .andEquals(PersonInfo::getDistrict, reqVo.getDistrict())
                .andEquals(PersonInfo::getCounty, reqVo.getCounty())
                .andEquals(PersonInfo::getCommunity, reqVo.getCommunity())
                .andEquals(PersonInfo::getIdNumber, f.getIdcard())
                .andEquals(PersonInfo::isDeleted, false);
        PersonInfoService personInfoService = NutzIocUtils.getBean(PersonInfoService.class);
        PersonInfo personInfo = personInfoService.fetch(cnd);
        if (personInfo != null) {
            reqVo.setId(personInfo.getId());
        }
        reqVo.setChildrenType(f.getChildrenType())
                .setOtherCategory(f.getOtherCategory())
                .setBusinessType(SupportBusinessTypeEnum.CHILDREN_VISITATION.getName());
        PersonInfo p = personInfoService.saveOrUpdatePerson(reqVo);
        return p.getId();
    }

    public Result<?> updateDictInit1() {
        List<Sys_dict> list = new ArrayList<>();
        Sys_dict dict = insertParentData("baiyun_children_visits_record", "白云区儿童探访记录");
        String[] names = {"儿童类别", "身体状况", "学工情况"};
        String[] codes = {"visits_children_category", "visits_children_physical_condition", "visits_children_work_situation"};
        insertChild(dict, names, codes, list);

        // 儿童类别
        SysDictService dictService = NutzIocUtils.getBean(SysDictService.class);
        Sys_dict sysDict1 = dictService.fetch(Cnd.where(Sys_dict::getCode, "=", "visits_children_category"));
        String[] names1 = {"孤儿", "事实无人抚养儿童", "留守儿童", "困难儿童", "其他"};
        String[] codes1 = {"children_category_orphan", "children_category_raising","children_category_behind", "children_category_difficult", "children_category_other"};
        insertChild(sysDict1, names1, codes1, list);
        return Result.data(list);
    }

    public Result<?> deleteManyTable() {
        Sql sql1 = Sqls.create("drop table shequ.sqfw_file_service_config");
        Sql sql2 = Sqls.create("drop table shequ.sqfw_del_log_record");
        Dao dao = NutzIocUtils.getBean(Dao.class);
        dao.execute(sql1);
        dao.execute(sql2);
        return Result.success();
    }
}















